package com.gesee.zyspps.utils;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.List;

import com.gesee.zyspps.ZSApplication;
import com.gesee.zyspps.db.CmsLine;
import com.gesee.zyspps.db.CmsPoints;

import jxl.Sheet;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.write.Label;
import jxl.write.WritableCell;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import android.content.Context;
import android.widget.Toast;

public class ExcelUtils {
	public static WritableFont arial14font = null;

	public static WritableCellFormat arial14format = null;
	public static WritableFont arial10font = null;
	public static WritableCellFormat arial10format = null;
	public static WritableFont arial12font = null;
	public static WritableCellFormat arial12format = null;

	public final static String UTF8_ENCODING = "UTF-8";
	public final static String GBK_ENCODING = "GBK";

	public static void format() {
		try {
			arial14font = new WritableFont(WritableFont.ARIAL, 14, WritableFont.BOLD);
			arial14font.setColour(jxl.format.Colour.LIGHT_BLUE);
			arial14format = new WritableCellFormat(arial14font);
			arial14format.setAlignment(jxl.format.Alignment.CENTRE);
			arial14format.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
			arial14format.setBackground(jxl.format.Colour.VERY_LIGHT_YELLOW);
			arial10font = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD);
			arial10format = new WritableCellFormat(arial10font);
			arial10format.setAlignment(jxl.format.Alignment.CENTRE);
			arial10format.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
			arial10format.setBackground(jxl.format.Colour.LIGHT_BLUE);
			arial12font = new WritableFont(WritableFont.ARIAL, 12);
			arial12format = new WritableCellFormat(arial12font);
			arial12format.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
		}
		catch (WriteException e) {

			e.printStackTrace();
		}
	}
	
	public static void readPointExcel(String path) {
        try {
         InputStream is = new FileInputStream(path);
         Workbook book = Workbook.getWorkbook(is);
         // 获得第一个工作表对象
         Sheet sheet = book.getSheet(0);
         int Rows = sheet.getRows();
         int Cols = sheet.getColumns();
         for (int j = 0; j < Rows; ++j) {
          //for (int i = 0; i < Cols; ++i) {
              //LogUtil.e(sheet.getCell(i,j).getContents());
        	  if(j!=0){
        		  CmsPoints points = new CmsPoints();
        		  points.setExpNO(sheet.getCell(0,j).getContents());
        		  points.setWDeep(sheet.getCell(1,j).getContents());
        		  points.setPCode(sheet.getCell(2,j).getContents());
        		  points.setSubsid(sheet.getCell(3,j).getContents());
        		  points.setFeature(sheet.getCell(4,j).getContents());
        		  points.setMapNoNum(Integer.parseInt(sheet.getCell(5,j).getContents()));
        		  points.setMtype(sheet.getCell(6,j).getContents());
        		  points.setWcover(sheet.getCell(7,j).getContents());
        		  points.setMsize(sheet.getCell(8,j).getContents());
        		  points.setWdepth(sheet.getCell(9,j).getContents());
        		  points.setHsize(sheet.getCell(10,j).getContents());
        		  points.setSblock(sheet.getCell(11,j).getContents());
        		  points.setOffset(StringUtil.isEmpty(sheet.getCell(12,j).getContents())?null:Double.valueOf(sheet.getCell(12,j).getContents()));
        		  points.setRoadName(sheet.getCell(13,j).getContents());
        		  points.setBCode(sheet.getCell(14,j).getContents());
        		  points.setBDate(StringUtil.isEmpty(sheet.getCell(15,j).getContents())?null:Double.valueOf(sheet.getCell(15,j).getContents()));
        		  points.setPAngle(StringUtil.isEmpty(sheet.getCell(16,j).getContents())?null:Double.valueOf(sheet.getCell(16,j).getContents()));
        		  points.setMapCode(StringUtil.isEmpty(sheet.getCell(17,j).getContents())?null:Double.valueOf(sheet.getCell(17,j).getContents()));
        		  points.setPunit(StringUtil.isEmpty(sheet.getCell(18,j).getContents())?null:Double.valueOf(sheet.getCell(18,j).getContents()));
        		  points.setPdate(StringUtil.isEmpty(sheet.getCell(19,j).getContents())?null:Double.valueOf(sheet.getCell(19,j).getContents()));
        		  points.setEname(StringUtil.isEmpty(sheet.getCell(20,j).getContents())?null:Double.valueOf(sheet.getCell(20,j).getContents()));
        		  points.setAname(StringUtil.isEmpty(sheet.getCell(21,j).getContents())?null:Double.valueOf(sheet.getCell(21,j).getContents()));
        		  points.setStreet(StringUtil.isEmpty(sheet.getCell(22,j).getContents())?null:Double.valueOf(sheet.getCell(22,j).getContents()));
        		  points.setQuarter(StringUtil.isEmpty(sheet.getCell(23,j).getContents())?null:Double.valueOf(sheet.getCell(23,j).getContents()));
        		  points.setCommunity(StringUtil.isEmpty(sheet.getCell(24,j).getContents())?null:Double.valueOf(sheet.getCell(24,j).getContents()));
        		  points.setProbe(StringUtil.isEmpty(sheet.getCell(25,j).getContents())?null:Double.valueOf(sheet.getCell(25,j).getContents()));
        		  points.setD_X(StringUtil.isEmpty(sheet.getCell(26,j).getContents())?null:Double.valueOf(sheet.getCell(26,j).getContents()));
        		  points.setD_Y(StringUtil.isEmpty(sheet.getCell(27,j).getContents())?null:Double.valueOf(sheet.getCell(27,j).getContents()));
        		  points.setRemark(sheet.getCell(28,j).getContents());
        		  points.setJslx(sheet.getCell(29,j).getContents());
        		  points.setTcsss(sheet.getCell(30,j).getContents());
        		  points.setTcsns(sheet.getCell(31,j).getContents());
        		  points.setJcjdj(sheet.getCell(32,j).getContents());
        		  points.setJcjzpbh(sheet.getCell(33,j).getContents());
        		  ZSApplication.getDaoSession(ZSApplication.getInstance().getConfig("newDBName")).getCmsPointsDao().insert(points);
        	//  }
          }
         }
         book.close();
        } catch (Exception e) {
         e.printStackTrace();
        }
      }
	
	
	public static void readLineExcel(String path) {
        try {
         InputStream is = new FileInputStream(path);
         Workbook book = Workbook.getWorkbook(is);
         // 获得第一个工作表对象
         Sheet sheet = book.getSheet(0);
         int Rows = sheet.getRows();
         int Cols = sheet.getColumns();
         for (int j = 0; j < Rows; ++j) {
        //  for (int i = 0; i < Cols; ++i) {
              //LogUtil.e(sheet.getCell(i,j).getContents());
        	  if(j!=0){
        		  CmsLine line = new CmsLine();
        		  line.setPipeLength(sheet.getCell(0,j).getContents());
        		  line.setS_Point(sheet.getCell(1,j).getContents());
        		  line.setE_Point(sheet.getCell(2,j).getContents());
        		  line.setS_Deep(sheet.getCell(3,j).getContents());
        		  line.setE_Deep(sheet.getCell(4,j).getContents());
        		  line.setPCode(sheet.getCell(5,j).getContents());
        		  line.setDType(sheet.getCell(6,j).getContents());
        		  line.setMaterial(sheet.getCell(7,j).getContents());
        		  line.setDSize(sheet.getCell(8,j).getContents());
        		  line.setPD_S(sheet.getCell(9,j).getContents());
        		  line.setPressure(sheet.getCell(10,j).getContents());
        		  line.setVoltage(sheet.getCell(11,j).getContents());
        		  line.setDirection(sheet.getCell(12,j).getContents());
        		  line.setCable(sheet.getCell(13,j).getContents());
        		  line.setHole(sheet.getCell(14,j).getContents());
        		  line.setUhole(sheet.getCell(15,j).getContents());
        		  line.setBelong(sheet.getCell(16,j).getContents());
        		  line.setBdate(sheet.getCell(17,j).getContents());
        		  line.setRoad(sheet.getCell(18,j).getContents());
        		  line.setS_D(sheet.getCell(19,j).getContents());
        		  line.setE_D(sheet.getCell(20,j).getContents());
        		  line.setStreet(sheet.getCell(21,j).getContents());
        		  line.setCommunity(sheet.getCell(22,j).getContents());
        		  line.setQuarter(sheet.getCell(23,j).getContents());
        		  line.setAname(sheet.getCell(24,j).getContents());
        		  line.setPunit(sheet.getCell(25,j).getContents());
        		  line.setPdate(sheet.getCell(26,j).getContents());
        		  line.setEname(sheet.getCell(27,j).getContents());
        		  line.setProbe(sheet.getCell(28,j).getContents());
        		  line.setRemark(sheet.getCell(29,j).getContents());
        		  line.setCableLines(sheet.getCell(30,j).getContents());
        		  line.setUseHole(sheet.getCell(31,j).getContents());
        		  line.setTotalHole(sheet.getCell(32,j).getContents());
        		  line.setLxbh1(sheet.getCell(33,j).getContents());
        		  line.setLxbh2(sheet.getCell(34,j).getContents());
        		  ZSApplication.getDaoSession(ZSApplication.getInstance().getConfig("newDBName")).getCmsLineDao().insert(line);
        	  }
          //}
         }
         book.close();
         ZSApplication.getInstance().setConfig("newDBName", "");
        } catch (Exception e) {
        	e.printStackTrace();
        }
      }

	public static void initExcel(String fileName, String[] colName) {
		format();
		WritableWorkbook workbook = null;
		try {
			File file = new File(fileName);
			if (!file.exists()) {
				file.createNewFile();
			}/*else{
			    boolean result = file.delete();
			    file.createNewFile();
			}*/
			workbook = Workbook.createWorkbook(file);
			WritableSheet sheet = workbook.createSheet("家庭帐务表", 0);
			sheet.addCell((WritableCell) new Label(0, 0, fileName, arial14format));
			for (int col = 0; col < colName.length; col++) {
				sheet.addCell(new Label(col, 0, colName[col], arial10format));
			}
			workbook.write();
		}
		catch (Exception e) {
			e.printStackTrace();
		}
		finally {
			if (workbook != null) {
				try {
					workbook.close();
				}
				catch (Exception e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}

	}

	@SuppressWarnings("unchecked")
	public static <T> void writeObjListToExcel(List<T> objList, String fileName, Context c) {
		if (objList != null && objList.size() > 0) {
			WritableWorkbook writebook = null;
			InputStream in = null;
			try {
				WorkbookSettings setEncode = new WorkbookSettings();
				setEncode.setEncoding(UTF8_ENCODING);
				in = new FileInputStream(new File(fileName));
				Workbook workbook = Workbook.getWorkbook(in);
				writebook = Workbook.createWorkbook(new File(fileName), workbook);
				WritableSheet sheet = writebook.getSheet(0);
				for (int j = 0; j < objList.size(); j++) {
					ArrayList<String> list=(ArrayList<String>) objList.get(j);
					for (int i = 0; i < list.size(); i++) {
						sheet.addCell(new Label(i, j+1, list.get(i), arial12format));
					}
				}
				writebook.write();
				Toast.makeText(c, "保存成功,保存文件在文件管理根目录下面的Zyspps文件夹下，", Toast.LENGTH_SHORT).show();
			}
			catch (Exception e) {
				e.printStackTrace();
			}
			finally {
				if (writebook != null) {
					try {
						writebook.close();
					}
					catch (Exception e) {
						e.printStackTrace();
					}

				}
				if (in != null) {
					try {
						in.close();
					}
					catch (IOException e) {
						e.printStackTrace();
					}
				}
			}

		}
	}

	public static Object getValueByRef(Class cls, String fieldName) {
		Object value = null;
		fieldName = fieldName.replaceFirst(fieldName.substring(0, 1), fieldName.substring(0, 1).toUpperCase());
		String getMethodName = "get" + fieldName;
		try {
			Method method = cls.getMethod(getMethodName);
			value = method.invoke(cls);
		}
		catch (Exception e) {
			e.printStackTrace();
		}
		return value;
	}
}
